
# library(pacman)
# p_load(dplyr, ggplot2, tidyr, cowplot, DBI, RSQLite) 
library(dplyr)
library(ggplot2)
library(tidyr)
library(cowplot)
library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), "iso_standards.sqlite") # Attaching to database

participation <- dbReadTable(con, "participants") # Extracting the TC-membership dataset

dbDisconnect(con) # Disconnecting from database

memberships <- participation %>%
  mutate(country = countrycode::countrycode(sourcevar = country,
                                            origin = "country.name",
                                            destination = "country.name")) # Using countrycode to ensure consistent country names

participation %>%
  filter(country %in% c("United States", "France", "Germany", "United Kingdom", "China")) %>% # Filtering out selected countries
  filter(membership %in% c("P-member", "Secretariat")) %>% # Extracting only P-members and Secretariat
  drop_na() %>% # Removing any missing 
  mutate(sector = ifelse(sector == "Business management and innovation", "Management and innovation", sector)) %>% # One sector changed name recently, renaming for consistency
  filter(year >= 2004) %>% # Including years after 2004
  group_by(year, country, sector) %>% 
  count() %>% # Counting number of participations in TCs per country, year and sector
  rename(` ` = country) %>% # No need for label on country in plot
  ggplot(aes(year, as.integer(n), group = ` `, color = ` `)) + # Plotting
  geom_line(aes(linetype = ` `), linewidth = 0.8) + 
  facet_wrap(~ sector, scales = "free", ncol = 3) + 
  scale_color_manual(breaks = c("China", "France", "Germany", "United Kingdom", "United States"),
                     values = c("#77001f", "#5f9d03", "#8ecbff", "#492071", "#f53f00")) + 
  scale_y_continuous(breaks = function(x) unique(floor(pretty(seq(min(x), (max(x) + 1) * 1.1))))) +
  labs(y = "", x = "") +
  theme_classic() +
  theme(axis.title.y = element_text(size=rel(1.2)),
        legend.text=element_text(size=rel(1.2)),
        legend.position = "bottom",
        legend.title = element_text(size = rel(1.2)),
        axis.text.x = element_text(size = rel(1.2), angle = 90, vjust = 0.5, hjust=1),
        axis.text.y = element_text(size = rel(1.2)),
        strip.text = element_text(size = 8)) 

